package com.hs.srm.admin.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.NoSuchElementException;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;

import com.hs.srm.base.common.utils.ImportDateInfo;

//import org.apache.poi.ss.util.CellAddress;

/**
 * @author clement
 * @description:
 * @date: Create in 02:14 2018/7/1
 */
public class ExcelHelpUtils {

	private static Logger logger = LoggerFactory.getLogger(ExcelHelpUtils.class);

	/**
	 * SAX方式读取文件
	 * @param filename 文件名
	 * @param tempFile 文件
	 * @param importDateInfo 导入信息
	 */
	public static void ReadScheduleTableSAX(String filename, File tempFile, ImportDateInfo importDateInfo)
			throws OpenXML4JException, ParserConfigurationException, SAXException, IOException {
		if(StringUtils.isEmpty(filename)) {
			return;
		}
		if(filename.endsWith("xls") || filename.endsWith("xlsx")) {
			if(filename.endsWith("xlsx")) {
				// Excel2007以后采用SAX方式
				try(OPCPackage pkg = OPCPackage.open(tempFile)) {
					ReadScheduleTableSAX(pkg, importDateInfo);
				} catch(InvalidFormatException e) {
					logger.debug("错误的文件格式 filename: {}", filename);
				} catch(IOException | OpenXML4JException | SAXException | ParserConfigurationException e) {
					logger.error("Excle读取失败, filename: {}", filename, e);
					throw e;
				}
			} else {
				// Excel2007以前采用传统方式
				try(InputStream is = new FileInputStream(tempFile)) {
					ReadScheduleTable(filename, is, importDateInfo);
				} catch(IOException e) {
					logger.error("Excle文件打开失败, file: {}", tempFile.getAbsolutePath(), e);
					throw e;
				}
			}
		} else {
			logger.debug("filename: {}", filename);
		}
	}

	private static void ReadScheduleTable(String filename, InputStream is, ImportDateInfo importDateInfo)
			throws IOException {
		if(StringUtils.isEmpty(filename)) {
			return;
		}
		if(filename.endsWith("xls") || filename.endsWith("xlsx")) {
			try(Workbook wb = filename.endsWith("xls") ? new HSSFWorkbook(is) : new XSSFWorkbook(is)) {
				importDateInfo.setItemName(readTitle(wb));
				importDateInfo.setItemValue(readContent(wb, importDateInfo.getItemName().size()));
			} catch(IOException e) {
				logger.error("Excle读取失败, filename: {}", filename, e);
				throw e;
			}
		} else {
			logger.debug("暂不支持此类型文件, filename: {}", filename);
		}

	}

	/**
	 * 全按字符串读取Excel数据内容
	 *
	 * @return Map 包含单元格数据内容的Map对象
	 */
	private static List<List<String>> readContent(Workbook wb, int size) {
		if(wb == null) {
			logger.error("文件读取失败, Workbook 为空");
		}
		Sheet sheet = wb.getSheetAt(0);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		logger.debug("总行数: {}", rowNum);
		List<List<String>> content = new ArrayList<>(rowNum - 1);
		// 正文内容应该从第二行开始,第一行为表头的标题
		for(int i = 1; i <= rowNum; i++) {
			Row row = sheet.getRow(i);
			/*int colNum = row.getPhysicalNumberOfCells();*/
			List<String> cellValue = new ArrayList<>(size);
			for(int j = 0; j < size; ++j) {
				Cell cell = row.getCell(j);
				String value = cell.getStringCellValue().trim();
				cellValue.add(value);
			}
			content.add(cellValue);
		}
		return content;
	}

	/**
	 * 读取工资单元格数据
	 *
	 * @param cell 单元格
	 * @return 字符串
	 */
	// private static String getScheduleTableCellValue(Cell cell) {
	// if (cell == null) {
	// return "";
	// }
	// String cellValue;
	// // 判断当前Cell的Type
	// switch (cell.getCellTypeEnum()) {
	// case BOOLEAN:
	// cellValue = String.valueOf(cell.getBooleanCellValue());
	// break;
	// case STRING:// 如果当前Cell的Type为STRING
	// // 取得当前的Cell字符串
	// cellValue = cell.getStringCellValue().trim();
	// break;
	// default:// 默认的Cell值
	// cellValue = "";
	// }
	// return cellValue;
	// }

	/**
	 * 读取Excel表格表头的内容
	 *
	 * @return String 表头内容的数组
	 */
	private static List<String> readTitle(Workbook wb) {
		if(wb == null) {
			logger.error("文件读取失败, Workbook 为空");
		}
		Sheet sheet = wb.getSheetAt(0);
		Row row = sheet.getRow(0);
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		logger.debug("标题总列数: {}", colNum);
		List<String> titles = new ArrayList<>(colNum);
		for(int i = 0; i < colNum; i++) {
			titles.add(row.getCell(i).getStringCellValue());
		}
		return titles;
	}

	private static void ReadScheduleTableSAX(OPCPackage pkg, ImportDateInfo importDateInfo) throws IOException,
			OpenXML4JException, SAXException, ParserConfigurationException {
		XSSFReader reader = new XSSFReader(pkg);
		StylesTable stylesTable = reader.getStylesTable();
		ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
		XMLReader sheetParser = fetchSheetParser(stylesTable, strings,
				new ExcelHelpUtils.ScheduleTableSheetContentsHandler(importDateInfo));
		sheetParser.parse(new InputSource(reader.getSheetsData().next()));
	}

	/**
	 * SAX方式读取薪资文件
	 * @param filename 文件名
	 * @param is       输入流
	 * @param importDateInfo 导入信息
	 */
	public static void ReadScheduleTableSAX(String filename, InputStream is, ImportDateInfo importDateInfo)
			throws OpenXML4JException, ParserConfigurationException, SAXException, IOException {

		if(StringUtils.isEmpty(filename)) {
			return;
		}
		if(filename.endsWith("xls") || filename.endsWith("xlsx")) {
			if(filename.endsWith("xlsx")) {
				// Excel2007以后采用SAX方式
				try(OPCPackage pkg = OPCPackage.open(is)) {
					ReadScheduleTableSAX(pkg, importDateInfo);
				} catch(InvalidFormatException e) {
					logger.debug("错误的文件格式 filename: {}", filename);
				} catch(IOException | OpenXML4JException | SAXException | ParserConfigurationException e) {
					logger.error("Excle 读取失败, filename: {}", filename, e);
				}
			} else {
				// Excel2007以前采用传统方式
				ReadScheduleTable(filename, is, importDateInfo);
			}
		} else {
			logger.debug("暂不支持此类型文件, filename: {}", filename);
		}

	}

	// 获取XML解析器
	public static XMLReader fetchSheetParser(StylesTable stylesTable, ReadOnlySharedStringsTable strings,
			XSSFSheetXMLHandler.SheetContentsHandler sheetContentsHandler) throws ParserConfigurationException,
			SAXException {
		// XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
		XMLReader parser = SAXHelper.newXMLReader();
		ContentHandler handler = new XSSFSheetXMLHandler(stylesTable, strings, sheetContentsHandler, false);
		parser.setContentHandler(handler);
		return parser;
	}

	private static class ScheduleTableSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

		private static Logger logger = LoggerFactory.getLogger(ExcelHelpUtils.ScheduleTableSheetContentsHandler.class);

		private int currentCol = -1;
		private List<List<String>> content;
		private List<String> rowValues;
		private ImportDateInfo importInfo;

		public ScheduleTableSheetContentsHandler(ImportDateInfo importDateInfo) {
			this.importInfo = importDateInfo;
			this.content = new ArrayList<>();
			this.importInfo.setItemValue(content);
		}

		@Override
		public void endRow(int rowNum) {
			// 标题行
			if(rowNum == 0) {
				this.importInfo.setItemName(rowValues);
			} else {// 数据行
				int delta = this.importInfo.getItemName().size() - rowValues.size();
				if(delta < 0) {
					logger.error("导入数据异常: 明细列数大于标题列数");
				} else if(delta > 0) {
					// 如果数据行少列, 则用空值补全
					for(int i = 0; i < delta; i++) {
						rowValues.add("");
					}
				}
				this.content.add(rowValues);
			}
		}

		@Override
		public void startRow(int rowNum) {
			this.currentCol = -1;
			this.rowValues = new ArrayList<>();
		}

		@Override
		public void cell(String cellReference, String formattedValue, XSSFComment xssfComment) {
			// if (cellReference == null) {
			// cellReference = new CellAddress(currentRow, currentCol)
			// .formatAsString();
			// }
			int thisCol = (new CellReference(cellReference)).getCol();
			int missedCols = thisCol - currentCol - 1;// 处理数据中间存在空白
			for(int i = 0; i < missedCols; i++) {
				this.rowValues.add("");
			}
			this.rowValues.add(formattedValue);
			currentCol = thisCol;
		}

		@Override
		public void headerFooter(String text, boolean isHeader, String tagName) {
			logger.debug("{}: {}, tag: {}", isHeader ? "Header" : "Footer", text, tagName);
		}

	}

	public static <T> List<T> readExcel(String filePath, int titleRow, int headerRow, Class<T> pojoClass) {
		if(StringUtils.isEmpty(filePath)) {
			return null;
		}
		return readExcel(new File(filePath), titleRow, headerRow, pojoClass);
	}

	public static <T> List<T> readExcel(MultipartFile file, int titleRow, int headerRow, Class<T> pojoClass) {
		if(file == null) {
			return null;
		}
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRow);
		params.setHeadRows(headerRow);
		List<T> list = null;
		try {
			list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
		} catch(NoSuchElementException e) {
			throw new RuntimeException("excel文件不能为空");
		} catch(Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		return list;
	}
	
	public static <T> List<T> readExcel(String filePath, Class<T> pojoClass) {
		return readExcel(filePath, 0, 1, pojoClass);
	}

	public static <T> List<T> readExcel(MultipartFile file, Class<T> pojoClass) {
		return readExcel(file, 0, 1, pojoClass);
	}
	
	public static <T> List<T> readExcel(File file, int titleRow, int headerRow, Class<T> pojoClass) {
		if(!file.exists()) {
			return null;
		}
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRow);
		params.setHeadRows(headerRow);
		List<T> list = null;
		try {
			list = ExcelImportUtil.importExcel(file, pojoClass, params);
		} catch(NoSuchElementException e) {
			throw new RuntimeException("excel文件不能为空");
		} catch(Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		return list;
	}
	
	public static <T> List<T> readExcel(File file, Class<T> pojoClass) {
		return readExcel(file, 0, 1, pojoClass);
	}

}
